package gov.nhso.prov.acc.ftp;
 
import gov.nhso.prov.acc.beans.Acc6FtpBean;
import gov.nhso.prov.acc.beans.Acc6ProvinceAccountBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.DateUtil;
import gov.nhso.prov.acc.utils.DigitUtil;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.net.ftp.FTPClient;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.JobKey;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.zehon.FileTransferStatus;
import com.zehon.ftp.FTP;

@SuppressWarnings({"rawtypes","unchecked"})
public class NHSO2BAAC extends FTPUtil  implements Job {
	public NHSO2BAAC() {
	}
	
	public void execute(JobExecutionContext context) throws JobExecutionException {
		Connection conn = null;
		ISUD isud = null;
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);

	        JobKey jobKey = context.getJobDetail().getKey();
	        System.out.println("NHSO2BAAC says: " + jobKey + " executing at " + new Date());
 
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			apab.setActive("Y");
			List lsapab = isud.selectList(apab);
			//List holiDay = new ArrayList(Arrays.asList("20130101","20130225","20130401","20130408","20130415","20130416","20130501","20130506","20130524","20130701","20130722","20130812","20131023","20131205","20131210","20131231"));
			int cf = 0;
			int countIn = 0;
			int countOut = 0;
			double sumAmtIn = 0;
			double sumAmtOut = 0;
			//TRANSFER
			for (int i = 0; i < lsapab.size(); i++) {

				StringBuffer header = new StringBuffer();
				StringBuffer detail = new StringBuffer();
				StringBuffer total = new StringBuffer();
				
				StringBuffer text = new StringBuffer();
				
				apab = (Acc6ProvinceAccountBean) lsapab.get(i);
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				header.append(this.fulFill("0", 1));
				header.append(this.fulFill("000001", 6));
				header.append(this.fulFill(apab.getProvinceVenderName(), 30));
				header.append(this.fulFill(apab.getProvinceAccountName(), 40));
				header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(this.fulFill(this.yyyy() + this.mm() + this.dd(), 8));
				header.append(this.fulFill(" ", 171));
				header.append(this.fulFill("100", 3));
				header.append(this.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('" + this.yyyy() + "-" + this.mm() + "-" + this.dd() + "', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE = '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				List lsatb = isud.listDataSql(sql);
				Map atb = null;
				double sum = 0d;
				String amount = "";
				String net = "";
				String branchCode = "";
				if (lsatb.size() > 0) {
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(this.fulFill("1", 1));
						detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(this.fulFill(this.yyyy() + this.mm() + this.dd(), 8));
						detail.append(this.fulFill(bankCode, 3));
						detail.append(this.fulFill(branchCode, 4));
						detail.append(this.fulFill(accNo, 16));
						detail.append(this.fulFill("C", 1));
						detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("N", 1));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("??", 2));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(this.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtIn += Double.parseDouble(amount);
						countIn++;
					}
				}
				//TOTAL RECORD
				total.append(this.fulFill("2",1));
				total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(this.fulFill(this.formatDecimal(sum,2), 15));
				total.append(this.fulFill(" ",242));
				total.append(this.fulFill(" ",128));
				
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = this.branchCode.split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}
					text.append(header).append(detail).append(total);
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + this.yyyy() + "/" + this.mm(); 
					String nameOfFile = "HSOIN4" + this.yyyy() + this.mm() + this.dd() +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					/*FileWriter file = new FileWriter(this.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					Acc6FtpBean afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setFtpStatus("W");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("W");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
				}
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				
				sql = "SELECT NVL(YYYY,TO_CHAR(SYSDATE, 'YYYY')) || MM || CASE WHEN LENGTH(DD) = 1 THEN '0'||DD ELSE DD END AS HOLIDAYS FROM ACC6_BAAC_HOLIDAY ";
				List holiday = isud.listDataSql(sql);
				List lsHoliday = new ArrayList();
				for (int x = 0 ; x < holiday.size() ; x++) {
					Map map  = (Map) holiday.get(x);
					lsHoliday.add(isud.chkData(map.get("HOLIDAYS")));
				}
				
				int count = 1;
				boolean result = this.getWeekEnd(this.yyyy(), this.mm(), this.getTomorrow());
				String nextDay = "";
				String strHoliDay = this.getNextDayFullFormat(count);
				String[] strArr = this.getTomorrow(count).split("\\/");
				while (result || lsHoliday.contains(strHoliDay)) {//เป็นวันเสาร์ -วันอาทิตย์ หรือวันหยุด
					count++;
					nextDay = this.getTomorrow(count);
					strArr = nextDay.split("\\/");
					result = this.getWeekEnd(strArr[2], strArr[1], strArr[0]);
					strHoliDay = this.getNextDayFullFormat(count);
				}
				
				header.append(this.fulFill("0", 1));
				header.append(this.fulFill("000001", 6));
				header.append(this.fulFill(apab.getProvinceVenderName(), 30));
				header.append(this.fulFill(apab.getProvinceAccountName(), 40));
				header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
				header.append(this.fulFill(" ", 171));
				header.append(this.fulFill("100", 3));
				header.append(this.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('" + this.yyyy() + "-" + this.mm() + "-" + this.dd() + "', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE <> '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				lsatb = isud.listDataSql(sql);
				atb = null;
				sum = 0d;
				amount = "";
				net = "";
				branchCode = "";
				if (lsatb.size() > 0) {
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(this.fulFill("1", 1));
						detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
						detail.append(this.fulFill(bankCode, 3));
						detail.append(this.fulFill(branchCode, 4));
						detail.append(this.fulFill(accNo, 16));
						detail.append(this.fulFill("C", 1));
						detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("N", 1));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("??", 2));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(this.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtOut += Double.parseDouble(amount);
						countOut++;
					}
				}
				//TOTAL RECORD
				total.append(this.fulFill("2",1));
				total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(this.fulFill(this.formatDecimal(sum,2), 15));
				total.append(this.fulFill(" ",242));
				total.append(this.fulFill(" ",128));
				
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = this.branchCode.split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}
					
					text.append(header).append(detail).append(total);
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + this.yyyy() + "/" + this.mm(); 
					String nameOfFile = "HSODC3" + strArr[2] + strArr[1] + strArr[0] +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					/*FileWriter file = new FileWriter(this.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					Acc6FtpBean afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setFtpStatus("W");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("W");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
				}
			}
			StringBuffer sb = new StringBuffer();
			DigitUtil du = new DigitUtil();
			sb.append("สรุปข้อมูลที่โอนเงิน ภายใน ธกส. จำนวน "+countIn+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtIn, 2)+" บาท  \n");
			sb.append("สรุปข้อมูลที่โอนเงิน ต่างธนาคาร "+countOut+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtOut, 2)+" บาท   \n\n");
			sb.append("ผู้ประสานงาน น.ส.ปิติมา บุญฤทธิ์ภักดี 090-197-5097 pitima.b@nhso.go.th , น.ส.ณิชารีย์ พิเศษภักดีพงศ์ 02-141-5058 nicharee.b@nhso.go.th");
			String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + this.yyyy() + "/" + this.mm(); 
			String nameOfFile = "SUMMARY"+this.yyyy()+this.mm()+this.dd();
			InputStream is = new ByteArrayInputStream(sb.toString().getBytes("TIS-620"));
			FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
			
			conn.commit();
		} catch (Exception e) {
			isud.printMessage();
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException esql) {
				esql.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void sendFTP(String date){
		Connection conn = null;
		ISUD isud = null;
		try {
			conn = DBConnection.getConnection();
			isud = ISUDFactory.create(conn);
 
			Acc6ProvinceAccountBean apab = new Acc6ProvinceAccountBean();
			apab.setActive("Y");
			List lsapab = isud.selectList(apab);
			//List holiDay = new ArrayList(Arrays.asList("20130101","20130225","20130401","20130408","20130415","20130416","20130501","20130506","20130524","20130701","20130722","20130812","20131023","20131205","20131210","20131231"));
			int cf = 0;
			int countIn = 0;
			int countOut = 0;
			double sumAmtIn = 0;
			double sumAmtOut = 0;
			String[] strArr = date.split("\\/");
			String yyyy = String.valueOf(Integer.parseInt(strArr[2]) - 543);
			String mm = strArr[1];
			String dd = strArr[0];
			
			//Delete FTP Data 
			Acc6FtpBean afb = new Acc6FtpBean();
			FTPUtil fu = new FTPUtil();
			DateUtil dateUtil = new DateUtil();
			List<Acc6FtpBean> lsFtp = new ArrayList<Acc6FtpBean>();
			//afb.setCreatedDate(this.dateUtil().changeDateToDB(date));
			lsFtp = isud.selectList(afb , " WHERE TRUNC(CREATED_DATE) = TO_DATE('"+dateUtil.changeFormatDate(date,"/","/")+"','DD.MM.YYYY') ");
			FTPClient client = new FTPClient();
			client.connect(fu.ftpHost);
			client.login(fu.ftpUserId, fu.ftpPasswd);
			String remoteFolder = fu.ftpNhso2BaacTranfer+"/"+yyyy+"/"+mm+"/";
			for (int n = 0; n < lsFtp.size(); n++) {
				Acc6FtpBean bean = (Acc6FtpBean)lsFtp.get(n);
				afb = new Acc6FtpBean();
				if (FTP.fileExists(remoteFolder, bean.getFileName(), this.ftpHost, this.ftpUserId, this.ftpPasswd)) {
					FTP.deleteFile(bean.getFileName(), remoteFolder, this.ftpHost, this.ftpUserId, this.ftpPasswd);
					afb.setFileName(bean.getFileName());
					isud.delete(afb);
				}
			}
			//end delete FTP Data 
			
			//TRANSFER
			for (int i = 0; i < lsapab.size(); i++) {

				StringBuffer header = new StringBuffer();
				StringBuffer detail = new StringBuffer();
				StringBuffer total = new StringBuffer();
				
				StringBuffer text = new StringBuffer();
				
				apab = (Acc6ProvinceAccountBean) lsapab.get(i);
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				header.append(this.fulFill("0", 1));
				header.append(this.fulFill("000001", 6));
				header.append(this.fulFill(apab.getProvinceVenderName(), 30));
				header.append(this.fulFill(apab.getProvinceAccountName(), 40));
				header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(this.fulFill(yyyy + mm + dd, 8));
				header.append(this.fulFill(" ", 171));
				header.append(this.fulFill("100", 3));
				header.append(this.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				String sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE = '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				List lsatb = isud.listDataSql(sql);
				Map atb = null;
				double sum = 0d;
				String amount = "";
				String net = "";
				String branchCode = "";
				if (lsatb.size() > 0) {
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(this.fulFill("1", 1));
						detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(this.fulFill(yyyy + mm + dd, 8));
						detail.append(this.fulFill(bankCode, 3));
						detail.append(this.fulFill(branchCode, 4));
						detail.append(this.fulFill(accNo, 16));
						detail.append(this.fulFill("C", 1));
						detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("N", 1));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("??", 2));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(this.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtIn += Double.parseDouble(amount);
						countIn++;
					}
				}
				//TOTAL RECORD
				total.append(this.fulFill("2",1));
				total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(this.fulFill(this.formatDecimal(sum,2), 15));
				total.append(this.fulFill(" ",242));
				total.append(this.fulFill(" ",128));
				
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = this.branchCode.split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}
					text.append(header).append(detail).append(total);
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + yyyy + "/" + mm; 
					String nameOfFile = "HSOIN4" + yyyy + mm + dd +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					//FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
					
					/*FileWriter file = new FileWriter(this.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					
					afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
								afb.setCreatedDate("TO_DATE('"+dateUtil.changeFormatDate(date,"/","/")+"','DD.MM.YYYY')");
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setCreatedDate("TO_DATE('"+dateUtil.changeFormatDate(date,"/","/")+"','DD.MM.YYYY')");
								afb.setFtpStatus("W");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("W");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
				}
				
				//HEADER RECORD sb.append
				header.delete(0, header.length());
				detail.delete(0, detail.length());
				total.delete(0, total.length());
				text.delete(0, text.length());
				
				sql = "SELECT NVL(YYYY,TO_CHAR(SYSDATE, 'YYYY')) || MM || CASE WHEN LENGTH(DD) = 1 THEN '0'||DD ELSE DD END AS HOLIDAYS FROM ACC6_BAAC_HOLIDAY ";
				List holiday = isud.listDataSql(sql);
				List lsHoliday = new ArrayList();
				for (int x = 0 ; x < holiday.size() ; x++) {
					Map map  = (Map) holiday.get(x);
					lsHoliday.add(isud.chkData(map.get("HOLIDAYS")));
				}
				
				String tomorrow = String.valueOf(Integer.parseInt(dd)+1);
				int count = 1;
				boolean result = this.getWeekEnd(yyyy, mm, tomorrow);
				String nextDay = "";
				String strHoliDay = this.getNextDayFullFormat(count);
				strArr = this.getTomorrow(count).split("\\/");
				while (result || lsHoliday.contains(strHoliDay)) {//เป็นวันเสาร์ -วันอาทิตย์ หรือวันหยุด
					count++;
					nextDay = this.getTomorrow(count);
					strArr = nextDay.split("\\/");
					result = this.getWeekEnd(strArr[2], strArr[1], strArr[0]);
					strHoliDay = this.getNextDayFullFormat(count);
				}
				
				header.append(this.fulFill("0", 1));
				header.append(this.fulFill("000001", 6));
				header.append(this.fulFill(apab.getProvinceVenderName(), 30));
				header.append(this.fulFill(apab.getProvinceAccountName(), 40));
				header.append(this.fulFill(apab.getProvinceAccountSaving(), 16));
				header.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
				header.append(this.fulFill(" ", 171));
				header.append(this.fulFill("100", 3));
				header.append(this.fulFill(" ", 125));
				header.append("\r\n");
				
				//DETAIL RECORDS
				sql = "SELECT B.VENDER_BANK_CODE, B.VENDER_BANK_BRANCH_CODE, B.VENDER_ACCOUNT, TO_CHAR(A.TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP " +
				" , A.EXPENSE_AMOUNT,  B.VENDER_ACCOUNT_NAME_TH,  B.VENDER_ACCOUNT_ADDRESS_TH,  B.VENDER_ACCOUNT_NAME_EN,  B.VENDER_ACCOUNT_ADDRESS_EN" +
				" FROM " + this.schema + ".ACC6_TRANSACTION A JOIN " + this.schema + ".ACC6_VENDER B ON A.VENDER_CODE = B.VENDER_CODE " +
				" WHERE A.EXPENSE_DATE = TO_DATE('" + yyyy + "-" + mm + "-" + dd + "', 'YYYY-MM-DD') " +
				" AND A.PROVINCE_ID = '" + apab.getProvinceId() + "' AND B.VENDER_BANK_CODE <> '034' " +
				" AND A.TRANSACTION_TYPE IN ('T', 'A')";
				lsatb = isud.listDataSql(sql);
				atb = null;
				sum = 0d;
				amount = "";
				net = "";
				branchCode = "";
				if (lsatb.size() > 0) {
					for (int j = 0; j < lsatb.size(); j++){
						atb = (Map) lsatb.get(j);
						amount = isud.chkData(atb.get("EXPENSE_AMOUNT"));
						net = amount;
						sum = sum + Double.parseDouble(amount);
						String accNo = isud.chkData(atb.get("VENDER_ACCOUNT"));
						String bankCode = isud.chkData(atb.get("VENDER_BANK_CODE"));
						
						branchCode = "";
						if (bankCode.equals("034")) {
							if (accNo.trim().length() == 10) {
								branchCode = "0" + accNo.substring(0, 3);
							} else if (accNo.trim().length() == 12) {
								branchCode = "0" + accNo.substring(2, 5);
							}
						} else {
							branchCode = isud.chkData(atb.get("VENDER_BANK_BRANCH_CODE"));
							if (branchCode != null && !branchCode.equals("")) {
								if (branchCode.trim().length() < 4) {
									for (int x=0;x<(4-branchCode.trim().length());x++) {
										branchCode = "0"+branchCode;
									}
								}
							}else {
								if (accNo.trim().length() == 10) {
									branchCode = "0" + accNo.substring(0, 3);
								} else if (accNo.trim().length() == 12) {
									branchCode = "0" + accNo.substring(2, 5);
								}
							}
						}

						detail.append(this.fulFill("1", 1));
						detail.append(this.fulFill(this.numberFormat("000000", String.valueOf(j + 2)), 6));
						detail.append(this.fulFill(strArr[2] + strArr[1] + strArr[0], 8));
						detail.append(this.fulFill(bankCode, 3));
						detail.append(this.fulFill(branchCode, 4));
						detail.append(this.fulFill(accNo, 16));
						detail.append(this.fulFill("C", 1));
						detail.append(this.fulFill(this.numberFormat("0000000000.00",  amount).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("N", 1));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000", "0"), 7));
						detail.append(this.fulFill(this.numberFormat("0000000000.00", net).replaceAll("\\.", ""), 12));
						detail.append(this.fulFill("??", 2));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_TH")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_TH")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_NAME_EN")), 40));
						detail.append(this.fulFill(isud.chkData(atb.get("VENDER_ACCOUNT_ADDRESS_EN")), 60));
						detail.append(this.fulFill(isud.chkData(atb.get("TRANSACTION_TIMESTAMP")), 81));
						detail.append(this.fulFill(" ",32));
						detail.append("\r\n");
						sumAmtOut += Double.parseDouble(amount);
						countOut++;
					}
				}
				//TOTAL RECORD
				total.append(this.fulFill("2",1));
				total.append(this.fulFill(this.numberFormat("000000", String.valueOf(lsatb.size() + 2)),6));
				total.append(this.fulFill(this.numberFormat("00000000", String.valueOf(lsatb.size())),8));
				total.append(this.fulFill(this.formatDecimal(sum,2), 15));
				total.append(this.fulFill(" ",242));
				total.append(this.fulFill(" ",128));
				
				if (lsatb.size() > 0) {
					cf++;
					branchCode = "";
					if (apab.getProvinceAccountSaving().trim().length() == 10) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(0, 3);
					} else if (apab.getProvinceAccountSaving().trim().length() == 12) {
						branchCode = "0" + apab.getProvinceAccountSaving().substring(2, 5);
					}
					String[] arrStr = this.branchCode.split("\\,");
					if (arrStr.length > 1) {
						for (int x=0;x<arrStr.length;x++) {
							String[] arrBuffer = arrStr[x].split("\\|");
							if (apab.getProvinceId().equals(arrBuffer[0])) {
								branchCode = arrBuffer[1];
								break;
							}
						}
					}
					
					text.append(header).append(detail).append(total);
					InputStream is = new ByteArrayInputStream(text.toString().getBytes("TIS-620"));
					String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + yyyy + "/" + mm; 
					String nameOfFile = "HSODC3" + strArr[2] + strArr[1] + strArr[0] +  "CA" + this.numberFormat("000", String.valueOf(cf)) + ".0592"+branchCode;
					//FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
					/*FileWriter file = new FileWriter(this.ftpBackup+"\\"+nameOfFile);
					BufferedWriter out = new BufferedWriter(file);
					out.write(text.toString());
					out.close();*/
					

					// For Test
					afb = new Acc6FtpBean();
					afb.setFileName(nameOfFile);
					List ls = isud.selectList(afb);
					if (ls.size() == 0) {
						try {
							int status = FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
							if (FileTransferStatus.SUCCESS == status) {
								afb.setFtpDesc("got ftp-ed successfully to  folder " + destFolder + "/" + nameOfFile);
								afb.setCreatedDate("TO_DATE('"+dateUtil.changeFormatDate(date,"/","/")+"','DD.MM.YYYY')");
							} else if(FileTransferStatus.FAILURE == status) {
								afb.setFtpDesc("Fail to ftp  to  folder "+destFolder);
								afb.setCreatedDate("TO_DATE('"+dateUtil.changeFormatDate(date,"/","/")+"','DD.MM.YYYY')");
								afb.setFtpStatus("W");
							}
						} catch (Exception e) {
							afb.setFtpDesc("Fail to ftp  to  folder "+e.getMessage());
							afb.setFtpStatus("W");
							e.printStackTrace();
						}
						isud.insert(afb);
					}
					//end test
				}
			}
			StringBuffer sb = new StringBuffer();
			DigitUtil du = new DigitUtil();
			sb.append("สรุปข้อมูลที่โอนเงิน ภายใน ธกส. จำนวน "+countIn+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtIn, 2)+" บาท  \n");
			sb.append("สรุปข้อมูลที่โอนเงิน ต่างธนาคาร "+countOut+" ราย จำนวนเงิน "+du.formatDecimal(sumAmtOut, 2)+" บาท   \n\n");
			sb.append("ผู้ประสานงาน น.ส.ปิติมา บุญฤทธิ์ภักดี 090-197-5097 pitima.b@nhso.go.th , น.ส.ณิชารีย์ พิเศษภักดีพงศ์ 02-141-5058 nicharee.b@nhso.go.th");
			String destFolder = "/"+this.ftpNhso2BaacTranfer+"/" + yyyy + "/" + mm; 
			String nameOfFile = "SUMMARY"+yyyy+mm+dd;
			InputStream is = new ByteArrayInputStream(sb.toString().getBytes("TIS-620"));
			FTP.sendFile(is, nameOfFile, destFolder, this.ftpHost,  this.ftpUserId,  this.ftpPasswd);
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException esql) {
				esql.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				isud = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
